오라클 사용자를 위한
SQL 튜닝 실무 사례
( PART III )
2002년 12월 30일
PART III
퀴즈 1. 중복된 로우만 골라서 삭제하라. 4
퀴즈 2. 정확히 10위까지만 구하라. 6
퀴즈 3. 공수별 비용 집계를 구하라. 11
퀴즈 4. 제품별 집계와 등급별 집계를 같이 구하라. 13
퀴즈 5. 당일 분, 일주일 분, 한달 분을 같이 구하라. 15
퀴즈 6. 한 테이블의 값을 가로로 두 번 표시하라. 17
퀴즈 7. 값이 바뀔 때 마다 일련번호를 다시 시작하라. 21
퀴즈 8. 불용 카드 수를 구하라. 26
퀴즈 9. 판매일자, 판매량 별로 누적 판매량을 구하라. 29
퀴즈 10. 4/4 분기 동안 점포별 매출액을 구하라. 33
퀴즈 11. 특정 품종에 대한 총 판매수량과 금액을 구하라. 36
퀴즈 12. 한번이라도 매출을 일으킨 고객을 구하라. 37
퀴즈 13. 매출액 20,000 이하 또는 없는 고객을 삭제하라. 38
퀴즈 14. 가산점과 신용 한도를 증가시켜라. 39
퀴즈 15. 매출 순위 10위까지의 고객 리스트를 작성하라. 40
퀴즈 16. 일별 총계 및 제품별 총계를 같이 구하라. 43
퀴즈 17. 입력 구분에 따라 서로 다른 총매출액을 구하라. 45
퀴즈 18. 년간 매출을 주 단위로 매출 구분하여 구하라 46
여기서 소개된 퀴즈들은 독자들의 SQL 이해력을 높이고 집합적 사고 능력을 함양 시키고자 준비한 것으로 간단하지만 많은 생각을 하여야만 정확한 답을 구할 수가 있다.
다만, 주의 할 점은 데이터가 많아졌을 때 나타날 수 있는 성능 상의 문제는 고려하지 않았으므로 실무 적용 시에는 다른 여러 가지 요소를 같이 고려하여 적용해야 한다는 것이다.
퀴즈 1. 중복된 로우만 골라서 삭제하라.
1) 배경
프로그램 개발을 위하여 샘플 데이터를 로딩한 뒤 PRIMARY KEY나 UNIQUE 인덱스를 만들고 싶지만 원시 데이터의 잘못으로 중복된 로우가 있으면 에러가 발생한다.
이럴 경우에 이를 확인하고 삭제하고 싶을 때가 있다.
EMP 테이블을 예를 들어 설명한다.
SQL> SELECT ROWID, ROWNUM, EMPNO, ENAME
2 FROM EMP
3 ORDER BY EMPNO, ROWID;
ROWID ROWNUM EMPNO ENAME
------------------ ------- ------- ----------
00024F04.0002.0005 3 7698 BLAKE
00024F04.0005.0005 4 7698 BLAKE <-- 삭제 대상
00024F04.0006.0005 5 7782 CLARK
00024F04.0001.0005 2 7788 SCOTT
00024F04.0007.0005 6 7788 SCOTT <-- 삭제 대상
00024F04.000B.0005 10 7788 SCOTT <-- 삭제 대상
00024F04.0008.0005 7 7839 KING
00024F04.0000.0005 1 7844 TURNER
00024F04.0009.0005 8 7844 TURNER <-- 삭제 대상
00024F04.000A.0005 9 7876 ADAMS
10 rows selected.
2) 요구 내용
위의 데이터 중 EMPNO 컬럼의 값이 중복된 로우에 대하여 ROWID 값이 큰 로우만 골라서 삭제하라.
3) 해답
중복된 로우를 찾아보기만 하는 경우
SELECT EMPNO, ENAME FROM EMP A
WHERE ROWID > ( SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
중복된 로우를 찾아서 ROWID가 더 큰 것을 삭제하는 경우 이 때 ROWID가 더 큰 것은 아마도 뒤에 생긴 데이터일 가능성이 더 많기 때문이다.
DELETE FROM EMP A
WHERE ROWID > ( SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
만약 나중에 들어온 데이터를 살리고 먼저 들어온 데이터를 삭제하고자 한다면 WHERE 조건의 부등호와 MIN을 MAX로 바꾸어서 다음과 같이 하면 된다.
DELETE FROM EMP A
WHERE ROWID < ( SELECT MAX(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
퀴즈 2. 정확히 10위까지만 구하라.
1) 배경
고정된 형태의 화면에서 항상 정확히 10 등까지의 데이터만 필요한 경우이다.
다음과 같은 EMP 테이블과 데이터가 있다.
SELECT ROWNUM, SAL, ENAME, JOB, EMPNO
FROM EMP
ORDER BY SAL DESC, ENAME;
ROWNUM 데이터는 ORDER BY에 의해 소트 가 수행되기 전에 붙여지므로 결과는 다음과 같이 나타난다.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
9 5000 KING PRESIDENT 7839
13 3000 FORD ANALYST 7902
8 3000 SCOTT ANALYST 7788
4 2975 JONES MANAGER 7566
6 2850 BLAKE MANAGER 7698
7 2450 CLARK MANAGER 7782
2 1600 ALLEN SALESMAN 7499
10 1500 TURNER SALESMAN 7844
14 1300 MILLER CLERK 7934 <--- 9 번째 로우 9 위
5 1250 MARTIN SALESMAN 7654 <--- 10 번째 로우 10 위 : CASE 2
3 1250 WARD SALESMAN 7521 <--- 11 번째 로우 10 위 : CASE 1
11 1100 ADAMS CLERK 7876 <--- 12 번째 로우 12 위
12 950 JAMES CLERK 7900
1 800 SMITH CLERK 7369
14 rows selected.
2) 요구 내용
위의 데이터를 이용하여 순위를 나타내는 컬럼과 함께 10위 까지 나오는 SQL문과 (위의 샘플 데이터 경우 WARD, 11 번째 로우) 정확히 10번째 로우까지만 나오는 (위의 샘플 데이터 경우 MARTIN, 10 번째 로우) SQL문을 작성하라.
즉, 다음과 같은 결과가 나오게 하라.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
1 5000 KING PRESIDENT 7839
2 3000 FORD ANALYST 7902
3 3000 SCOTT ANALYST 7788
4 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450 CLARK MANAGER 7782
7 1600 ALLEN SALESMAN 7499
8 1500 TURNER SALESMAN 7844
9 1300 MILLER CLERK 7934
10 1250 MARTIN SALESMAN 7654
11 1250 WARD SALESMAN 7521
11 rows selected.
3) 해답
 CASE 1 : 상위 10위 까지 의 로우를 출력
SELECT ROWNUM, SAL, ENAME, JOB, EMPNO
FROM EMP E1
WHERE 10 > ( SELECT COUNT(*)
FROM EMP E2
WHERE E2.SAL > E1.SAL )
ORDER BY SAL DESC ;
다음과 같은 출력이 나온다.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
8 5000 KING PRESIDENT 7839
7 3000 SCOTT ANALYST 7788
10 3000 FORD ANALYST 7902
3 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450 CLARK MANAGER 7782
1 1600 ALLEN SALESMAN 7499
9 1500 TURNER SALESMAN 7844
11 1300 MILLER CLERK 7934
2 1250 WARD SALESMAN 7521
4 1250 MARTIN SALESMAN 7654
11 rows selected.
그러나 이 출력은 ROWNUM 값이 소트 되기 전에 붙으므로 원하는 결과와 다르다.
ROWNUM을 순차적으로 표시하기 위해 IN-LINE VIEW를 하나 더 씌운다.
그러나 이렇게 되면 IN-LINE VIEW 내에서 ORDER BY 문을 사용할 수 없게 되므로 GROUP BY 문을 활용하여 소트 기능을 대신한다.
SELECT ROWNUM AS RANK, -SAL SAL, ENAME, JOB, EMPNO
FROM ( SELECT -SAL SAL, ENAME, JOB, EMPNO
FROM EMP E1
WHERE 10 > ( SELECT COUNT(*)
FROM EMP E2
WHERE E2.SAL > E1.SAL )
GROUP BY -SAL, ENAME, JOB, EMPNO) ;
다음과 같은 출력이 나온다.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
1 5000 KING PRESIDENT 7839
2 3000 SCOTT ANALYST 7788
3 3000 FORD ANALYST 7902
4 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450 CLARK MANAGER 7782
7 1600 ALLEN SALESMAN 7499
8 1500 TURNER SALESMAN 7844
9 1300 MILLER CLERK 7934
10 1250 WARD SALESMAN 7521
11 1250 MARTIN SALESMAN 7654
11 rows selected.
 CASE 2 : 상위 10째 까지 의 로우를 출력
SELECT ROWNUM AS RANK, -SAL SAL, ENAME, JOB, EMPNO
FROM ( SELECT -SAL SAL, ENAME, JOB, EMPNO
FROM EMP E1
WHERE 10 > ( SELECT COUNT(*)
FROM EMP E2
WHERE E2.SAL > E1.SAL )
GROUP BY -SAL, ENAME, JOB, EMPNO)
WHERE ROWNUM <= 10 ;
최종 결과는 위와 같다.
정확히 10 번째 로우에서 자르기 위해 ROWNUM <= 10 조건을 추가하였다.
그러나 처음부터 10 번째 로우에서 자르겠다면 굳이 위와 같이 하지 않고 간단히 아래와 같이 하는 방법도 있다.
SELECT ROWNUM, -SAL SAL, ENAME, JOB, EMPNO
FROM (SELECT -SAL SAL, ENAME, JOB, EMPNO
FROM EMP
GROUP BY -SAL, ENAME, JOB, EMPNO)
WHERE ROWNUM <= 10 ;
위의 두 SQL문의 결과는 다음과 같다.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
1 5000 KING PRESIDENT 7839
2 3000 SCOTT ANALYST 7788
3 3000 FORD ANALYST 7902
4 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450 CLARK MANAGER 7782
7 1600 ALLEN SALESMAN 7499
8 1500 TURNER SALESMAN 7844
9 1300 MILLER CLERK 7934
10 1250 MARTIN SALESMAN 7654
10 rows selected.
퀴즈 3. 공수별 비용 집계를 구하라.
1) 배경
어떤 건설 회사의 전표 테이블에서 투입된 공임에 대한 데이터를 다음과 같이 관리하고 있다.
동일한 성격의 일을 하고 있는데 작업 번호 101 번은 1 명이 투입되어 6 시간 걸려서 끝냈고 그때의 비용은 200,000 원 이었고, 102 번은 3 명이 투입되어 2 시간 만에 끝냈으며 그 때의 비용은 230,000원 이다.
작업 번호 인원 시간 비용
101 1 6 200,000
102 3 2 230,000
103 2 3 220,000
104 6 1 300,000
105 1 8 300,000
106 5 3 500,000
107 2 6 400,000
108 1 15 550,000
109 1 12 450,000
110 3 5 530,000
... ... ...
150 4 6 900,000
2) 요구 내용
작업 번호 101, 102번을 예로 들면 이 두 작업은 서로 다른 것 같지만 "인원*시간" 을 한 값이 같으므로 같은 공수로 볼 수 있다.
이와 같은 맥락으로 보면 작업 번호 103, 104번도 동일한 공수이다.
따라서 공수가 6인 작업의 비용을 모두 구하면 200,000 + 230,000 + 220,000 + 300,000 = 950,000원이 된다.
공수 비용합계 요구 내용
6 950,000 왼편의 표와 같이 모든 공수의 종류를 구하고 그 공수별로 들어간 비용의 합계를 구하라.
8 300,000
12 850,000
15 1,580,000
... .....
24 900,000
3) 해답
SELECT 시간*인원 "공수", SUM(비용) "금액"
FROM 전표테이블
WHERE 발생일 LIKE '9610%'
GROUP BY 시간*인원 ;
GROUP BY 시간*인원 라는 아이디어를 이용하여 하나의 SQL 문으로 풀 수 있다.
퀴즈 4. 제품별 집계와 등급별 집계를 같이 구하라.
1) 배경
어떤 제조 회사의 제품별 판매 내용이 다음과 같다.
1996년 10월에 판매된 제품의 내용은 다음과 같다.
제품명 모델명 수량 매출액 집계구분
HP HJ300 1 10,000 제
품
별
집
계
HP HJ300 2 20,000
HP HA120 1 5,000
HP HB580 1 20,000
HP HB580 3 6,000
LD A530G 1 100,000 모
델
명
사
용
모
델
별
집
계
LD A530G 3 300,000
LD A210K 2 400,000
LD A210K 3 600,000
PP P530C 10 100,000
PP P530D 5 25,000 기
타
PP R640A 10 15,000
PP R650B 20 20,000
2) 요구 내용
위와 같이 판매일자와 함께 각각의 제품에 대한 판매 내용이 있을 때 전체 판매에 대한 수량과 매출액 등의 통계자료를 보고싶은 것이다.
단, 통계자료를 볼 때 제품명이 HP인 제품은 저가여서 제품별로 만 봐도 되니까 모델 별은 무시하고 제품별로 만 수량과 매출액 합계를 내고, 제품명이 LD인 제품은 고가이므로 각 모델별로 수량과 매출액 합계를 내고, 제품명이 PP인 제품은 저가 단품들 이므로 모든 모델을 하나로 몰아서 기타로 하여 수량과 매출액에 대한 합계를 구하라.
그러나 제품명이 PP인 제품 중에서 모델명이 P530C인 제품은 최근에 개발한 신제품이므로 기타에 합치지 말고 별도로 빼내어 모델명까지 구분하도록 하라.
3) 해답
GROUP BY 와 DECODE 의 절묘한 조화로 다음과 같이 하나의 SQL 문으로 답을 구할 수 있다.
SELECT DECODE(제품,'HP', 제품, 'LD', 모델명, 'PP',
DECOE(모델명, 'P530C', 모델명, '기타')), <--- 제품이 'PP'인 경우
SUM(수량), SUM(매출액)
FROM 매출테이블
WHERE 매충일 LIKE '199610%'
GROUP BY DECODE(제품,'HP', 제품, 'LD', 모델명, 'PP',
DECODE(모델명, 'P530C', 모델명, '기타')) ;
퀴즈 5. 당일 분, 일주일 분, 한달 분을 같이 구하라.
1) 배경
다음과 같은 판매 데이터가 들어있는 테이블이 있다.
온라인 화면에서 사용되며 통계 테이블은 별도로 준비되어 있지 않다.
SALEDATE
SALEDEPT ITEM CHULQTY SALEAMT
... ... ... ... ...
19961021 100 P100 100 1000
19961021 100 P102 50 500
19961021 101 Q110 10 2000
19961021 101 Q111 10 2000
19961021 102 R110 20 1000
19961022 100 P100 200 2000 최근 최고분
19961022 100 P102 100 1000
19961022 101 Q110 5 1000 최근 최고분
... ... ... ... ...
2) 요구 내용
위 테이블을 이용하여 당일분 합계, 최근 일주일분 합계, 가장 최근의 최고 판매량분, 월간 합계를 같이 구하라.
통상 " 가장 최근의 최고 판매량분" 은 그날의 판매 데이터 중 최고 판매량 이지만 그날의 데이터가 아직 들어가지 않은 상태라면 어제의 판매량 중 최고의 데이터를 가져온다.
만약 오늘이 1996년 10월 22일 이라면 부서별 가장 최근 및 최고 분은 위의 표시된 로우와 같다.
3) 해답
DECODE, SIGN, MAX 함수를 사용하여 다음과 같이 하나의 SQL 문으로 해결할 수 있다.
SELECT SALEDEPT,
SUM(DECODE(SALEDATE, TO_CHAR(SYSDATE,'YYYYMMDD'),SALEQTY,0)) AS '당일합계' ,
SUM(DECODE(SIGN (8 - (SYSDATE - TO_DATE(SALEDATE,'YYYYMMDD'))
), 1, SALEQTY)) AS '일주일분합계' ,
SUBSTR(MAX(SALEDATE || CHULQTY),9,20) AS '최근 최고분'
SUM(SALEAMT) AS '월간합계'
FROM 판매테이블
WHERE SALEDATE LIKE '9610%'
GROUP BY SALEDEPT ;
퀴즈 6. 한 테이블의 값을 가로로 두 번 표시하라.
1) 배경
일반 프로그램이나 웹 브라우저에서 동일한 테이블의 데이터를 옆으로 반복하여 표시하고 할 때 사용할 수 있다.
다음과 같은 데이터가 있다고 본다.
SQL> SELECT EMPNO,ENAME FROM EMP ;
EMPNO ENAME
------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
11 rows selected.
2) 요구 내용
위와 같은 결과가 다음과 같이 한 테이블의 값을 가로로 두 번 표시하라.
ORD EMPNO ENAME JOB EMPNO ENAME JOB
------- ------- ---------- --------- ------- ---------- ---------
1 7369 SMITH CLERK 7499 ALLEN SALESMAN
2 7521 WARD SALESMAN 7566 JONES MANAGER
3 7654 MARTIN SALESMAN 7698 BLAKE MANAGER
4 7782 CLARK MANAGER 7788 SCOTT ANALYST
5 7839 KING PRESIDENT 7844 TURNER SALESMAN
6 7876 ADAMS CLERK
6 rows selected.
3) 해답
STEP 1 : 우선 ROWNUM을 짝수, 홀수로 구분하여 가로로 표시한다.
SQL> SELECT ROWNUM,TRUNC((ROWNUM+1)/2) AS ORD,
2 DECODE(MOD(ROWNUM,2),1,EMPNO,NULL) AS FIRST_EMPNO,
3 DECODE(MOD(ROWNUM,2),0,EMPNO,NULL) AS SECOND_EMPNO
4 FROM EMP ;
ROWNUM ORD FIRST_EMPNO SECOND_EMPNO
------- ------- ----------- ------------
1 1 7369
2 1 7499
3 2 7521
4 2 7566
5 3 7654
6 3 7698
7 4 7782
8 4 7788
9 5 7839
10 5 7844
11 6 7876
11 rows selected.
STEP 2 : 빈 컬럼을 없애기 위해 SUM(DECODE...) 를 사용한다.
SQL> SELECT TRUNC((ROWNUM+1)/2) AS ORD,
2 SUM(DECODE(MOD(ROWNUM,2),1,EMPNO,NULL)) AS FIRST_EMPNO,
3 SUM(DECODE(MOD(ROWNUM,2),0,EMPNO,NULL)) AS SECOND_EMPNO
4 FROM EMP
5 GROUP BY TRUNC((ROWNUM+1)/2) ;
ORD FIRST_EMPNO SECOND_EMPNO
------- ----------- ------------
1 7369 7499
2 7521 7566
3 7654 7698
4 7782 7788
5 7839 7844
6 7876
6 rows selected.
STEP 3 : 해당 EMPNO 컬럼에 ENAME, JOB 데이터를 덧붙인다.
SQL> SELECT ORD,
2 F_EMPNO AS EMPNO,A.ENAME,A.JOB,
3 S_EMPNO AS EMPNO,B.ENAME,B.JOB
4 FROM ( SELECT ORD,
5 SUM(FIRST_EMPNO) AS F_EMPNO,
6 SUM(SECOND_EMPNO) AS S_EMPNO
7 FROM ( SELECT TRUNC((ROWNUM+1)/2) AS ORD,
8 DECODE(MOD(ROWNUM,2),1,EMPNO,NULL) AS FIRST_EMPNO,
9 DECODE(MOD(ROWNUM,2),0,EMPNO,NULL) AS SECOND_EMPNO
10 FROM EMP)
11 GROUP BY ORD ), EMP A, EMP B
12 WHERE F_EMPNO = A.EMPNO
13 AND S_EMPNO = B.EMPNO(+)
14 ORDER BY ORD ;
OUTER 조인을 사용하지 않으면 SECOND_EMPNO 컬럼의 값이 NULL인 경우 전체 로우의 값이 나오지 않게 되므로 반드시 S_EMPNO와 조인되는 B.EMPNO 컬럼에 (+) 사인을 추가해야 한다.
ORD EMPNO ENAME JOB EMPNO ENAME JOB
------- ------- ---------- --------- ------- ---------- ---------
1 7369 SMITH CLERK 7499 ALLEN SALESMAN
2 7521 WARD SALESMAN 7566 JONES MANAGER
3 7654 MARTIN SALESMAN 7698 BLAKE MANAGER
4 7782 CLARK MANAGER 7788 SCOTT ANALYST
5 7839 KING PRESIDENT 7844 TURNER SALESMAN
6 7876 ADAMS CLERK
6 rows selected.
퀴즈 7. 값이 바뀔 때 마다 일련번호를 다시 시작하라.
1) 배경
다음과 같은 데이터가 들어있는 테이블이 있다.
이해를 돕기 위해 DUMMY 컬럼을 넣어서 작성하였지만 실제로는 DUMMY 컬럼은 없다.
참고로 ROWNUM 컬럼도 같이 보기 바란다.
SQL> DESC TSTBL;
Name Null? Type
------------------------------- -------- ----
PK1 CHAR(3)
PK2 CHAR(5)
DUMMY NUMBER
SQL> SELECT PK1, PK2, DUMMY, ROWNUM FROM TSTBL
2 ORDER BY PK1, PK2 ;
PK1 PK2 DUMMY ROWNUM
--- ----- ------- -------
A01 AS011 1 1
A01 AS012 2 2
A01 AS013 3 3
A01 AS014 4 4
A01 AS015 5 5
A02 AS021 1 6
A02 AS022 2 7
A02 AS023 3 8
A03 AS021 1 9
B01 BS011 1 10
B01 BS012 2 11
B01 BS013 3 12
B01 BS014 4 13
B01 BS015 5 14
B01 BS016 6 15
B01 BS017 7 16
B01 BS018 8 17
B01 BS018 8 17
B02 BS021 1 18
B02 BS022 2 19
B02 BS023 3 20
B02 BS024 4 21
C01 CS011 1 22
C01 CS012 2 23
C02 CS021 1 24
C02 CS022 2 25
C02 CS023 3 26
C02 CS024 4 27
27 rows selected.
2) 요구 내용
위의 데이터 중 PK1, PK2 컬럼만 사용하여 PK1 컬럼의 값이 바뀔 때 마다 일련번호가 1 부터 새롭게 시작되도록 하라.
즉, 일련번호가 나오는 컬럼을 SEQ 컬럼이라 하면 PK1, PK2 컬럼만 사용하여 SEQ 컬럼이 값이 DUMMY 컬럼의 값처럼 나오게 하라.
3) 해답
우선 기존에 나오는 ROWNUM 컬럼의 값과 PK1으로 GROUP BY하여 나오는 COUNT(*) 값을 이용하여 구한다.
STEP 1 : 우선 GROUP BY 한 값을 COUNT 한 뒤 ROWNUM을 붙여서 이 결과를 SELF 조인 하여 바로 전 PK1 값 까지 의 COUNT 값을 구한다.
SQL> SELECT B.PK1, B.RNUM AS B_RNUM, C.RNUM AS C_RNUM, C.CNT
2 FROM (SELECT ROWNUM AS RNUM, PK1, CNT
3 FROM (SELECT PK1, COUNT(*) AS CNT FROM TSTBL
4 GROUP BY PK1)) B,
5 (SELECT ROWNUM AS RNUM, PK1, CNT
6 FROM (SELECT PK1, COUNT(*) AS CNT FROM TSTBL
7 GROUP BY PK1)) C
8 WHERE B.RNUM > C.RNUM ;
PK1 B_RNUM C_RNUM CNT
--- ------- ------- -------
A02 2 1 5 <-- A01 값의 COUNT(*) <-- A02
A03 3 1 5 <-- A01 값의 COUNT(*) <-- A03
A03 3 2 3 <-- A02 값의 COUNT(*)
B01 4 1 5 <-- A01 값의 COUNT(*) <-- B01
B01 4 2 3 <-- A02 값의 COUNT(*)
B01 4 3 1 <-- A03 값의 COUNT(*)
B02 5 1 5 <-- A01 값의 COUNT(*) <-- B02
B02 5 2 3 <-- A02 값의 COUNT(*)
B02 5 3 1 <-- A03 값의 COUNT(*)
B02 5 4 8 <-- B01 값의 COUNT(*)
C01 6 1 5 <-- A01 값의 COUNT(*) <-- C01
C01 6 2 3 <-- A02 값의 COUNT(*)
C01 6 3 1 <-- A03 값의 COUNT(*)
C01 6 4 8 <-- B01 값의 COUNT(*)
C01 6 5 4 <-- B02 값의 COUNT(*)
C02 7 1 5 <-- A01 값의 COUNT(*) <-- C02
C02 7 2 3 <-- A02 값의 COUNT(*)
C02 7 3 1 <-- A03 값의 COUNT(*)
C02 7 4 8 <-- B01 값의 COUNT(*)
C02 7 5 4 <-- B02 값의 COUNT(*)
C02 7 6 2 <-- C01 값의 COUNT(*)
STEP 2 : 바로 이전 PK1 값까지의 누계 COUNT 값을 알기 위해 PK1 값과 B.RNUM 값으로 GROUP BY 한다.
SQL> SELECT B.PK1, B.RNUM, SUM(C.CNT) AS ACC_SUM
2 FROM (SELECT ROWNUM AS RNUM, PK1, CNT
3 FROM (SELECT PK1,COUNT(*) AS CNT FROM TSTBL
4 GROUP BY PK1)) B,
5 (SELECT ROWNUM AS RNUM, PK1, CNT
6 FROM (SELECT PK1,COUNT(*) AS CNT FROM TSTBL
7 GROUP BY PK1)) C
8 WHERE B.RNUM > C.RNUM
9 GROUP BY B.PK1, B.RNUM ;
PK1 RNUM ACC_SUM
--- ------- -------
A02 2 5
A03 3 8
B01 4 9
B02 5 17
C01 6 21
C02 7 23
6 rows selected.
STEP 3 : 바로 이전 PK1 값까지의 누계 값과 원래의 ROWNUM을 이용하여 최종결과를 구한다. 이때 OUTER 조인을 사용해야만 "PK1 = A01" 값이 빠지지 않게 된다.
SQL> SELECT A.PK1, A.RNUM - NVL(ACC_SUM,0) AS SEQ, A.PK2, A.DUMMY
2 FROM (SELECT PK1,ROWNUM AS RNUM,PK2,DUMMY
3 FROM (SELECT PK1,PK2,DUMMY
4 FROM TSTBL
5 GROUP BY PK1,PK2,DUMMY)) A,
6 (SELECT B.PK1,B.RNUM,SUM(C.CNT) AS ACC_SUM
7 FROM (SELECT ROWNUM AS RNUM, PK1,CNT
8 FROM (SELECT PK1,COUNT(*) AS CNT FROM TSTBL
9 GROUP BY PK1)) B,
10 (SELECT ROWNUM AS RNUM, PK1,CNT
11 FROM (SELECT PK1,COUNT(*) AS CNT FROM TSTBL
12 GROUP BY PK1)) C
13 WHERE B.RNUM > C.RNUM
14 GROUP BY B.PK1,B.RNUM) D
15 WHERE A.PK1 = D.PK1(+)
16 ORDER BY A.PK1, A.RNUM - NVL(ACC_SUM,0) ;
실행 결과는 다음과 같으며 SEQ 컬럼은 PK1 값이 바뀌면 일련번호를 다시 시작하는 것을 볼 수 있다.
DUMMY 컬럼은 SEQ 값이 정확히 잘 나왔는가를 검토하는 목적으로 추가된 것이고 실제의 테이블에는 없다.
또한 SQL 컬럼도 IN-LINE VIEW 내의 PSEUDO 컬럼인 ROWNUM 컬럼에서 나왔으므로 실제 테이블에는 없다.
PK1 SEQ PK2 DUMMY
--- ------- ----- -------
A01 1 AS011 1
A01 2 AS012 2
A01 3 AS013 3
A01 4 AS014 4
A01 5 AS015 5
A02 1 AS021 1
A02 2 AS022 2
A02 3 AS023 3
A03 1 AS021 1
B01 1 BS011 1
B01 2 BS012 2
B01 3 BS013 3
B01 4 BS014 4
B01 5 BS015 5
B01 6 BS016 6
B01 7 BS017 7
B01 8 BS018 8
B02 1 BS021 1
B02 2 BS022 2
B02 3 BS023 3
B02 4 BS024 4
C01 1 CS011 1
C01 2 CS012 2
C02 1 CS021 1
C02 2 CS022 2
C02 3 CS023 3
C02 4 CS024 4
27 rows selected.
퀴즈 8. 불용 카드 수를 구하라.
1) 배경
카드 사에서 본인 카드와 그것의 가족 카드를 연관하여 일정 기간 사용되지 않은 카드 수를 계산한다.
다음과 같은 샘플 데이터로 단순화 시켜서 카드 수를 구한다.
카드정보 테이블 카드사용실적 테이블
카드번호
본인카드번호 카드번호 사용금액
101 NULL 101 10,000
102 101 101 5,500
103 101 105 35,000
104 NULL 07 70,000
105 NULL
106 NULL
107 104
108 104
109 NULL
110 NULL
2) 요구 내용
위 샘플 데이터로부터 보면 사용 실적이 없는 카드 번호의 단순 리스트는 102, 103, 104, 106, 108, 109, 110등 모두 7개 카드지만 여기서 문제는 102, 103 카드는 이것의 본인 카드인 101이 사용 실적이 있으므로 실제적으로 답에서 빠져야 하며 104 카드도 역시 해당 가족 카드인 107이 사용 실적이 있으므로 104, 108도 역시 답에서 빠져야 하기 때문에 결과적으로 사용 실적이 없는 카드의 답은 106, 109, 110 모두 3개의 카드가 된다.
이를 구하는 쿼리를 작성하라.
3) 해답
SELECT 카드번호
FROM 카드정보 A
WHERE NOT EXISTS (SELECT X FROM 카드정보 B
WHERE EXISTS ( SELECT X FROM 카드사용실적 C
WHERE C.카드번호 = B.카드번호)
START WITH B.카드번호 = NVL(A.본인카드번호, A.카드번호)
CONNECT BY PRIOR B.카드번호 = B.본인카드번호) ;
위에서 START WITH B.카드번호 = NVL(A.본인카드번호, A.카드번호) 절은 연관 카드를 찾아서 수행되도록 하여 요구 사항의 누락이 없도록 본인 카드로 부터 시작하여 카드 사용 실적을 찾도록 하기 위함이며 START WITH ~ CONNECT BY PRIOR은 이책의 PART II 사례 23번, 26번을 참조하여 이해를 구하거나 오라클 "Sql Language Reference Manual" Chapter 4 Commands 의 SELECT 부분을 참조하기 바란다.
퀴즈 9. 판매일자, 판매량 별로 누적 판매량을 구하라.
1) 배경
다음과 같은 데이터가 들어있는 테이블이 있다.
SQL> DESC BFUN1;
Name Null? Type
------------------------------- -------- ----
판매일자 Not Null VARCHAR2(8)
판매량 NUMBER
SQL> SELECT * FROM BFUN1;
판매일자 판매량
-------- -------
19970225 10
19970214 40
19970218 23
19970220 65
19970208 43
19970203 22
19970211 18
19970218 15
19970213 60
19970218 19
10 개의 행이 선택되었습니다.
2) 요구 내용
위의 10개 데이터를 판매일자를 기준으로 소트 하였을 때 그에 해당하는 판매량 까지 구분하여 판매량을 누적한 누적 판매량을 같이 표시하도록 하라.
즉, 다음과 같이 데이터가 나오도록 하라.
판매일자 판매량 누적판매량
-------- ------- ----------
19970203 22 22
19970208 43 & |
